[Oracle] dbms_metadata. get_ddl
- 2021-08-12 03:56:25
- OfStack
dbms_metadata. get_ddl () is used to get the DDL of the object as follows.
Note: In sqlplus, in order to better demonstrate DDL, the following parameters need to be set:
1) Get the DDL for tables, indexes, views, stored procedures, and functions
The following script is used to get the DDL of all tables, indexes, views, stored procedures and functions under an schema
2) Get the DDL of the tablespace
Get the DDL for a single tablespace:
Get DDL for all tablespaces:
3) Obtain the user's DDL
Get the DDL for a single user:
Get DDL for all users:
Note: In sqlplus, in order to better demonstrate DDL, the following parameters need to be set:
set line 200
set pagesize 0
set long 99999
set feedback off
set echo off
1) Get the DDL for tables, indexes, views, stored procedures, and functions
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;
select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;
The following script is used to get the DDL of all tables, indexes, views, stored procedures and functions under an schema
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner) FROM DBA_TABLES u;
SELECT DBMS_METADATA.GET_DDL('VIEW',u.view_name,u.owner) FROM DBA_VIEWS u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner) FROM DBA_INDEXES u;
select dbms_metadata.get_ddl('PROCEDURE',u.object_name, u.owner,) from dba_objects u where u.object_type = 'PROCEDURE';
select dbms_metadata.get_ddl('FUNCTION',u.object_name, u.owner,) from dba_objects u where u.object_type = 'FUNCTION';
spool off;
2) Get the DDL of the tablespace
Get the DDL for a single tablespace:
select dbms_metadata.get_ddl('TABLESPACE','TBS_NAME') from dual;
Get DDL for all tablespaces:
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
3) Obtain the user's DDL
Get the DDL for a single user:
select dbms_metadata.get_ddl('USER','EPAY_USER') from dual;
Get DDL for all users:
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;